<?php

define("SORT_ASCENDING", 1);
define("SORT_DESCENDING", 2);


class database {
	var $host;
	var $user;
	var $password;
	var $database;
	public $localConnect;
	
	function database()
	{
		$this->host = "localhost";
		$this->user = "root";
		$this->password = "lizardman";
		$this->database = "ffl2007_2008";
	}
	
	function __destruct() 
	{
		//$this->closeDatabase($this->localConnect);
	}
	
	function createDatabase($databaseYear, $maxTransfers, $maxMembers, $maxWeeks, $maxTeamValue)
	{
		$error = 0;
		$connect = $this->connectToDatabase();
		$this->localConnect = $connect;
		
		// In the form of ffl2007_2008
		$nextDatabaseYear = $databaseYear+1;
		$databaseName = "ffl".$databaseYear."_".$nextDatabaseYear;
		$query = "CREATE DATABASE ".$databaseName;
		if (mysqli_query($connect, $query))
		{
			echo "Database ".$databaseName." created successfully";
			$this->selectDatabase($connect, $databaseName);
			$error |= $this->createPlayerList($connect, $maxWeeks);
			$error |= $this->createMemberList($connect, $maxTransfers, $maxWeeks);
			$error |= $this->createConfigTable($connect);
		} 
		else 
		{
			echo 'Error creating database: ' . mysqli_error($connect) . "\n";
		}
		return $databaseName;
	}
	
	function deleteDatabase()
	{
		$error = 0;
	}
	
	function deleteTableEntries($connect, $table)
	{
		$query = "DELETE FROM ".$table;
		$error = mysqli_query($connect, $query);
		if ($error == 1)
		{
			echo "<BR>Table deleted: $table";
		}
		else
		{
			echo "<BR>FAILED to delete table $table";
		}
	}
	
	function DoesTableExist($connect, $table)
	{
		$query = "SHOW TABLES LIKE '".$table."'";
		
		if( mysqli_num_rows( mysqli_query($connect, $query) ))
		{
			return TRUE;
		}
		else
		{
			return FALSE;
		}
	}
	
	function createInjuryTable($connect)
	{
		// team1 v team2 is the draw for the first round
		// round1 to round n contains the week number that each round will take place on
		$query = "CREATE TABLE injury ( loginName	VARCHAR(8)	NOT NULL,
											 password	VARCHAR(10)	NOT NULL,
											 player VARCHAR(100)	NOT NULL PRIMARY KEY,
											 team	CHAR(4),
											 injury	VARCHAR(100),
											 returnDate VARCHAR(100))
											 ";
		
		
		$error = mysqli_query($connect, $query);
		if ($error == 1)
		{
			echo "<BR>Table created: injury";
		}
		else
		{
			echo "<BR>FAILED to create table ".$error;
		}
	}
	
	function createTandbergCup($connect, $numberRounds)
	{
		// team1 v team2 is the draw for the first round
		// round1 to round n contains the week number that each round will take place on
		$query = "CREATE TABLE tandbergCup ( loginName	VARCHAR(8)	NOT NULL,
											 password	VARCHAR(10)	NOT NULL,
											 matchNumber INT(3) UNSIGNED PRIMARY KEY,
											 teamOne	CHAR(4),
											 teamTwo	CHAR(4) ";
		for ($round = 1; $round <= $numberRounds; $round++)
		{	
			$query = $query.", round".$round." INT(2)";
		}
		$query = $query.")";
		
		$error = mysqli_query($connect, $query);
		if ($error == 1)
		{
			echo "<BR>Table created: tandbergCup";
		}
		else
		{
			echo "<BR>FAILED to create table ".$error;
		}
	}
	
	function createPlayerList($connect, $maxWeeks)
	{
		$query = "CREATE TABLE playerList (	loginName	VARCHAR(8)	NOT NULL,
											password	VARCHAR(10)	NOT NULL,
											playerCode	CHAR(4)	PRIMARY KEY,
											playerActive CHAR(1),
											playerName	VARCHAR(30),
											playerTeam	CHAR(3),
											playerValue	DECIMAL(4,1),
											playerPosition	CHAR(3),
											playerLastYearScore	CHAR(3)";
		for ($week = 1; $week <= $maxWeeks; $week++)
		{	
	 		if ($week < 10)
				$query = $query.", week0".$week." INT(4)";
			else
				$query = $query.", week".$week." INT(4)";
		}
		$query = $query.")";		
		$error = mysqli_query($connect, $query);
	}
	
	function createMemberList($connect, $maxTransfers, $maxWeeks)
	{
		$query = "CREATE TABLE memberList (	loginName	VARCHAR(8)	NOT NULL,
											password	VARCHAR(10)	NOT NULL,
											memberName	VARCHAR(20)	PRIMARY KEY,
											teamName	VARCHAR(50),
											moneyPaid	INT(2) UNSIGNED,
											active		INT(1) UNSIGNED,
											miniPop	INT(2) UNSIGNED";
		for ($playerNumber = 1; $playerNumber <= ($maxTransfers + PLAYERSPERTEAM); $playerNumber++)
		{	
	 		if ($playerNumber < 10)
				$query = $query.", playerCode0".$playerNumber." INT(4) UNSIGNED";
			else
				$query = $query.", playerCode".$playerNumber." INT(4) UNSIGNED";
		}
		for ($playerNumber = 1; $playerNumber <= ($maxTransfers + PLAYERSPERTEAM); $playerNumber++)
		{	
	 		if ($playerNumber < 10)
				$query = $query.", playerWeekIn0".$playerNumber." INT(2) UNSIGNED";
			else
				$query = $query.", playerWeekIn".$playerNumber." INT(2) UNSIGNED";
		}
		for ($playerNumber = 1; $playerNumber <= ($maxTransfers + PLAYERSPERTEAM); $playerNumber++)
		{	
	 		if ($playerNumber < 10)
				$query = $query.", playerWeekOut0".$playerNumber." INT(2) UNSIGNED";
			else
				$query = $query.", playerWeekOut".$playerNumber." INT(2) UNSIGNED";
		}
		for ($week = 1; $week <= $maxWeeks; $week++)
		{	
	 		if ($week < 10)
				$query = $query.", captainWeek0".$week." INT(4) UNSIGNED";
			else
				$query = $query.", captainWeek".$week." INT(4) UNSIGNED";
		}
		$query = $query.",userName VARCHAR(10) NOT NULL, userPass VARCHAR (10) 	NOT NULL)";
		$error = mysqli_query($connect, $query);
		if (!$error)
		{
			echo "<b>Error ".mysqli_errno($connect).": ".mysqli_error($connect)."</b>";
			echo "<BR><BR>".$query;
		}
		else
		{
			echo "<BR>Memberlist created.";
		}
	}
	
	function createConfigTable($connect)
	{
		$query = "CREATE TABLE config (	loginName		VARCHAR(8)	NOT NULL,
										password		VARCHAR(10)	NOT NULL,
										startYear		INT(4) UNSIGNED PRIMARY KEY,
										startMonth		INT(2) UNSIGNED,
										startDay		INT(2) UNSIGNED,
										maxTeamValue	INT(2) UNSIGNED,
										maxTransfers	INT(2) UNSIGNED,
										playersPerTeam	INT(2) UNSIGNED,
										maxWeeks		INT(2) UNSIGNED,
										entryFee		INT(2) UNSIGNED,
										transferFee		INT(2) UNSIGNED,
										numberPrizes	INT(2) UNSIGNED,
										teamOfTheMonth	INT(1) UNSIGNED,
										captains		INT(1) UNSIGNED,
										userlogins		INT(1) UNSIGNED
										)";
										
		$error = mysqli_query($connect, $query);
	}
	
	function createCaptainLog($connect)
	{
		$query = "CREATE TABLE captainLog ( loginName	VARCHAR(8)	NOT NULL,
											 password	VARCHAR(10)	NOT NULL,
											 log		INT(4) UNSIGNED PRIMARY KEY,
											 dateYear	INT(4) UNSIGNED,
											 dateMonth	INT(2) UNSIGNED,
											 dateDay	INT(2) UNSIGNED,
											 timeHours	INT(2) UNSIGNED,
											 timeMinutes	INT(2) UNSIGNED,
											 timeSeconds	INT(2) UNSIGNED,
											 week			INT(2) UNSIGNED,
											 teamName	VARCHAR(50),
											 captainOut	CHAR(4),
											 captainIn	CHAR(4) )";
		
		$error = mysqli_query($connect, $query);
		if ($error == 1)
		{
			echo "<BR>Table created: tandbergCup";
		}
		else
		{
			echo "<BR>FAILED to create table ".$error;
		}
	}
	
	function createTransferLog($connect)
	{
		$query = "CREATE TABLE transferLog ( loginName	VARCHAR(8)	NOT NULL,
											 password	VARCHAR(10)	NOT NULL,
											 log		INT(4) UNSIGNED PRIMARY KEY,
											 dateYear	INT(4) UNSIGNED,
											 dateMonth	INT(2) UNSIGNED,
											 dateDay	INT(2) UNSIGNED,
											 timeHours	INT(2) UNSIGNED,
											 timeMinutes	INT(2) UNSIGNED,
											 timeSeconds	INT(2) UNSIGNED,
											 week			INT(2) UNSIGNED,
											 teamName	VARCHAR(50),
											 playerOut	CHAR(4),
											 playerIn	CHAR(4) )";
		
		$error = mysqli_query($connect, $query);
		if ($error == 1)
		{
			echo "<BR>Table created: tandbergCup";
		}
		else
		{
			echo "<BR>FAILED to create table ".$error;
		}
	}
	
	function connectToDatabase()
	{
		if (!$connect = mysqli_connect($this->host, $this->user, $this->password))
		{
			// Error handle for connect
			$message = mysql_error();
			echo $message."<BR>";
			die();
		}	
		//$db = mysqli_select_db($connect, $this->database);
		$this->localConnect = $connect;
		
		return $connect;
	}
	
	function selectDatabase($connect, $database)
	{
		$db = mysqli_select_db($connect, $database/*$this->database*/);
		
		return $db;
	}
	
	function closeDatabase($connect)
	{
		mysqli_close($connect);
	}
	
	function selectFromDatabase($connect, $table, $selectArray, $whereString, $whereVar, $orderBy, $orderName)
	{
		// Build the query with all the select variables
		if ($whereString == NULL || $whereVar == NULL)
		{
			$whereString = "loginName";
			$whereVar = $this->user;
		}

		$query = "SELECT ";
		for ($i = 0; $i < sizeof($selectArray); $i++)
		{
			if ($i > 0)
				$query = $query.",";
			$query = $query." ".$selectArray[$i];
		}
		//$query = $query." FROM ".$table." WHERE ".$whereString." = '".$whereVar."'";
		
		// This $query does a full text seach as it includes "" instead of ''
		$query = $query." FROM ".$table." WHERE ".$whereString." = \"".$whereVar."\"";
		
		if ($orderBy == SORT_ASCENDING)
		{
			$query = $query." ORDER BY '".$orderName."'";
		}
		else if ($orderBy == SORT_DESCENDING)
		{
			$query = $query." ORDER BY '".$orderName."' DESC";
		}
		//echo "<BR>".$query;
		$result = mysqli_query($connect, $query);
		if($result == 0)
		{
			echo "<b>Error ".mysqli_errno($connect).": ".mysqli_error($connect)."</b>";
			$error = 1;
		}
		elseif (@mysqli_num_rows($result) == 0)
		{
			//echo("<b>Query completed. No results returned.</b><br>");
			$error = 2;
		}
		else
		{
			//echo "<BR>good";
		}
		
		return $result;
	}
	
	function insertConfig($connect, $startYear, $startMonth, $startDay, $maxTeamValue, $maxTransfers, 
		$playersPerTeam, $maxWeeks, $entryFee, $transferFee, $numberPrizes, $teamOfTheMonth, $captains, $userlogins)
	{										
		$query = "INSERT INTO config (
			loginName, password, startYear, startMonth, startDay, 
			maxTeamValue, maxTransfers, playersPerTeam, maxWeeks, entryFee, 
			transferFee, numberPrizes, teamOfTheMonth, captains, userlogins) 
											
			VALUES ('".$this->user."', '".$this->password."', '$startYear', '$startMonth', '$startDay', 
			'$maxTeamValue', '$maxTransfers', '$playersPerTeam', '$maxWeeks', '$entryFee', 
			'$transferFee', '$numberPrizes', '$teamOfTheMonth', '$captains', '$userlogins')";

		$result = mysqli_query($connect, $query);
		if ($result == 1)
		{
			echo "<BR>Config has been added: ".$startYear." ".$startMonth." ".$startDay." ".$maxTeamValue." ".$maxTransfers;
		}
		else
		{
			echo "<BR>ERROR: Config has not been added errno ".mysqli_errno($connect).": ".mysqli_error($connect);
		}
	}
	
	function insertDraw($connect, $numbers, $numMembers)
	{				
		$match = 0;
		for ($count = 0; $count < $numMembers; $count+=2)
		{	$match++;
			$query = "INSERT INTO tandbergCup (loginName, password, matchNumber, teamOne, teamTwo) 
				VALUES ('".$this->user."', '".$this->password."', '".$match."', '".$numbers[$count]."', '".$numbers[$count+1]."')";
			//echo "<BR>".$query;
			$result = mysqli_query($connect, $query);
			if ($result == 1)
			{
				echo "<BR>Added draw: ".$numbers[$count]." v ".$numbers[$count+1];
			}
			else
			{
				echo "<BR>ERROR: Failed to add draw: ".$numbers[$count]." v ".$numbers[$count+1];
			}
		}
	}
	
	function insertInjury($connect, $player, $team, $injury, $returnDate)
	{				
		$query = "INSERT INTO injury (loginName, password, player, team, injury, returnDate) 
			VALUES ('".$this->user."', '".$this->password."', \"".$player."\", '".$team."', '".$injury."', '".$returnDate."')";
		//echo "<BR>".$query;
		$result = mysqli_query($connect, $query);
		if ($result == 1)
		{
			echo "<BR>Added injury: ";
			echo "player:$player team:$team injury:$injury returnDate:$returnDate";
		}
		else
		{
			echo "<BR>ERROR: Failed to add injury: ";
			echo "player:$player team:$team injury:$injury returnDate:$returnDate";
		}
	}
	
	function insertPlayer($connect, $playerCode, $playerName, $playerTeam, $playerValue, $playerPosition, $playerLastYearScore)
	{
		$playerActive = 1; // Be default it's 1 (1 = player active, 0 = player inactive)
		$query = "INSERT INTO playerlist (loginName, password, playerCode, playerActive, playerName, playerTeam, playerValue, playerPosition, playerLastYearScore) 
			VALUES ('".$this->user."', '".$this->password."', '$playerCode', '$playerActive', '$playerName', '$playerTeam', '$playerValue', '$playerPosition', '$playerLastYearScore')";
			
		$result = mysqli_query($connect, $query);
		if ($result == 1)
		{
			echo "<BR>Player has been added: ".$playerName." ".$playerCode." ".$playerTeam." ".$playerValue." ".$playerPosition." ".$playerLastYearScore;
		}
		else
		{
			echo "<BR>ERROR: Player has not been added ".$playerName." ".$playerCode." ".$playerTeam." ".$playerValue." ".$playerPosition." ".$playerLastYearScore;
		}
	}
	
	function insertMember($maxWeeks, $connect, $memberName, $memberTeam, $moneyPaid, $miniPop,
		$player1,$player2,$player3,$player4,$player5,$player6,$player7,$player8,$player9,$player10,$player11,$captain,$userName,$userPass)
	{
		$active = 1;
		$defaultWeekIn = 1;
		
		$query = "INSERT INTO memberlist (
		loginName, password, memberName, teamName, moneyPaid, active, miniPop, 
		playerCode01, playerCode02, playerCode03, playerCode04, playerCode05, playerCode06, 
		playerCode07, playerCode08, playerCode09, playerCode10, playerCode11, 
		playerWeekIn01, playerWeekIn02, playerWeekIn03, playerWeekIn04, playerWeekIn05, playerWeekIn06,
		playerWeekIn07, playerWeekIn08, playerWeekIn09, playerWeekIn10, playerWeekIn11";
		
		for ($week = 1; $week <= $maxWeeks; $week++)
		{	
	 		if ($week < 10)
				$query = $query.", captainWeek0".$week;
			else
				$query = $query.", captainWeek".$week;
		} 
		
		$query = $query.", userName, userPass)
		VALUES ('".$this->user."', '".$this->password."', '$memberName', '$memberTeam', '$moneyPaid', '$active', '$miniPop', 
		'".$player1."', 
		'".$player2."',
		'".$player3."',
		'".$player4."',
		'".$player5."',
		'".$player6."',
		'".$player7."',
		'".$player8."',
		'".$player9."',
		'".$player10."',
		'".$player11."', 
		'$defaultWeekIn', '$defaultWeekIn', '$defaultWeekIn', '$defaultWeekIn', '$defaultWeekIn', '$defaultWeekIn',
		'$defaultWeekIn', '$defaultWeekIn', '$defaultWeekIn', '$defaultWeekIn', '$defaultWeekIn',";
		
		for ($week = 1; $week <= $maxWeeks; $week++)
		{	
			$query = $query." '$captain',";
		} 
		
		$query = $query." '$userName', '$userPass')";
		
		echo $query;
		
		$result = mysqli_query($connect, $query);
		if ($result == 1)
		{
			echo"<BR>Member has been added: ".$memberName.":".$teamName;
			echo"<BR>Username:".$userName." password:".$userPass;
		}
		else
		{
			echo"<BR>ERROR: Member has not been added";
			echo "<b>Error ".mysqli_errno($connect).": ".mysqli_error($connect)."</b>";
		}
	}
	
	function insertTandbergCupWeeks($connect, $round, $week)
	{
		// Fill in the top row only
		$query = "UPDATE tandbergCup SET round".$round." = '".$week."' limit 1";
		
		echo "<BR>".$query;
		$result = mysqli_query($connect, $query);
		if ($result == 1)
		{
			echo"<BR>Week".$week." has been added for round ".$round;
		}
		else
		{
			echo"<BR>ERROR: Week has not been added";
		}
	}
	
	function insertCaptainLog($connect, $dateYear, $dateMonth, $dateDay, $timeHours, $timeMinutes, 
		$timeSeconds, $week, $teamName, $captainOut, $captainIn, $log)
	{					
		// Find index by calculating how many rows we currently have and add one
		$query = "INSERT INTO captainLog (
			loginName, password, log, dateYear, dateMonth, dateDay, 
			timeHours, timeMinutes, timeSeconds, week, teamName, 
			captainOut, captainIn) 
											
			VALUES ('".$this->user."', '".$this->password."', '$log', '$dateYear', '$dateMonth', '$dateDay', 
			'$timeHours', '$timeMinutes', '$timeSeconds', '$week', '$teamName', 
			'$captainOut', '$captainIn')";

		$result = mysqli_query($connect, $query);
		//echo $query;
		if ($result == 1)
		{
			echo "<BR>Log has been added: ".$startYear." ".$startMonth." ".$startDay." ".$maxTeamValue." ".$maxTransfers;
		}
		else
		{
			echo "<BR>ERROR: Log has not been added errno ".mysqli_errno($connect).": ".mysqli_error($connect);
		}
	}
	
	function insertTransferLog($connect, $dateYear, $dateMonth, $dateDay, $timeHours, $timeMinutes, 
		$timeSeconds, $week, $teamName, $playerOut, $playerIn)
	{										
		$query = "INSERT INTO transferLog (
			loginName, password, dateYear, dateMonth, dateDay, 
			timeHours, timeMinutes, timeSeconds, week, teamName, 
			playerOut, playerIn) 
											
			VALUES ('".$this->user."', '".$this->password."', '$dateYear', '$dateMonth', '$dateDay', 
			'$timeHours', '$timeMinutes', '$timeSeconds', '$week', '$teamName', 
			'$playerOut', '$playerIn')";

		$result = mysqli_query($connect, $query);
		if ($result == 1)
		{
			echo "<BR>Log has been added: ".$startYear." ".$startMonth." ".$startDay." ".$maxTeamValue." ".$maxTransfers;
		}
		else
		{
			echo "<BR>ERROR: Log has not been added errno ".mysqli_errno($connect).": ".mysqli_error($connect);
		}
	}
	
	function doQuery($connect, $query)
	{
		$error = mysqli_query($connect, $query);
		
		if($error == 0)
 		{
		    echo "<b>Error ".mysqli_errno($connect).": ".mysqli_error($connect)."</b>";
		}
		else if (@mysqli_num_rows($error) == 0)
		{
		    echo("<b>Query completed. No results returned.</b><br>");
		}
 		else
 		{
  			echo "<table border='1'><thead><tr>";
			$finfo = mysqli_fetch_fields($error);
			for($i = 0; $i < mysqli_num_fields($error); $i++)
			{
				echo "<th>" .$finfo[$i]->name."</th>";
			}
			echo "  </tr></thead><tbody>";
			for ($i = 0; $i < mysqli_num_rows($error); $i++)
			{
				echo "<tr>";
				$row = mysqli_fetch_row($error);
				for($j = 0; $j < mysqli_num_fields($error); $j++)
				{
					echo("<td>" . $row[$j] . "</td>");
				}
				echo "</tr>";
			}
			echo "</tbody></table>";
 		}
	}
	
	// Function returns the active database
	function getActiveDatabase($connect)
	{
		$db = $this->selectDatabase($connect, 'ffl_config');
		
		if ($db != 0)
		{
			$selectArray[0] = '*';
			$result = $this->selectFromDatabase($connect, 'configlist', $selectArray, NULL, NULL, NULL, NULL);

			$row = mysqli_fetch_array($result, MYSQLI_ASSOC);
		
			return $row['activeDatabase'];
		}
	}
	
	function updatePlayer($connect, $selectColumn, $setValue, $localPlayerCode)
	{
		// Update this entry
		$query = "UPDATE playerlist SET ".$selectColumn." = '".$setValue."' WHERE playerCode='".$localPlayerCode."'";
		$result = mysqli_query($connect, $query);
		
		if ($result == 1)
		{
			echo"<BR>Player ".$localPlayerCode." has been updated, changed ".$selectColumn." to ".$setValue;
		}
		else
		{
			echo"<BR>ERROR: Player has not been updated.";
		}
	}
	
	function updateMember($connect, $selectColumn, $setValue, $localMemberName)
	{	
		// Update this entry
		if ($setValue=="")
		{
			$query = "UPDATE memberlist SET ".$selectColumn." = NULL WHERE memberName='".$localMemberName."'";
			//echo $query;
		}
		else
		{
			$query = "UPDATE memberlist SET ".$selectColumn." = '".$setValue."' WHERE memberName='".$localMemberName."'";
			//echo $query;
		}
		$result = mysqli_query($connect, $query);
		
		if ($result == 1)
		{
			echo"<BR>Member has been updated.";
		}
		else
		{
			echo"<BR>ERROR: Member has not been updated.";
		}
	}
	
	function GetConfigItem($connect)
	{
		$selectArray[0] = '*';
		$result = $this->selectFromDatabase($connect, 'config', $selectArray, NULL, NULL, NULL, NULL);
		
		$finfo = mysqli_fetch_fields($result);
		$row = mysqli_fetch_array($result, MYSQLI_ASSOC);

		return $row;
	}
	
	function GetMaxWeeks($connect)
	{
		$row = $this->GetConfigItem($connect);
		return $row['maxWeeks'];
	}
	
	function GetUserLoginEnabled($connect)
	{
		$row = $this->GetConfigItem($connect);
		return $row['userlogins'];
	}
	
	function GetCaptainsEnabled($connect)
	{
		$row = $this->GetConfigItem($connect);
		return $row['captains'];
	}
	
	function GetNumberPlayersForPosition($position, $connect)
	{
		$selectArray[0] = '*';
		$result = $this->selectFromDatabase($connect, 'playerlist', $selectArray, "playerPosition", $position, NULL, NULL);

		return mysqli_num_rows($result);
	}
}
?>
